# !/usr/bin/env python
# -*- coding:utf-8 -*-
# project name: agent_interface_automation
# author: "Lei Yong"
# creation time: 2020-11-26 14:36
# Email: leiyong711@163.com

import json
import time
import datetime
import sqlite3
import traceback
import requests
import jsonschema
# from log import logger
from sqlite_pool import sqlite3, Pool


# 针对requests 2.5.0版本以下的,不包含2.5.0版本
from requests.adapters import HTTPAdapter
from urllib3.exceptions import InsecureRequestWarning
requests.urllib3.disable_warnings(InsecureRequestWarning)

# 针对requests 2.5.0版本及以上的版本
# from requests.packages.urllib3.exceptions import InsecureRequestWarning
# requests.packages.urllib3.disable_warnings(InsecureRequestWarning)

# 创建接口测试用例表
CREATE_INTERFACE_TABLE_SQL = "create table %s (id integer primary key,case_id integer,url varchar(255)," \
                             "method varchar(15) NOT NULL,request_data TEXT,headers TEXT NOT NULL," \
                             "required_struct TEXT,response_result TEXT,check_result TEXT,response_time TEXT," \
                             "test_error TEXT,create_time TIMESTAMP default (datetime('now', 'localtime'))) "

# 创建用例执行统计表 用于统计及管理用例
CREATE_RUNNING_STATISTICS_TABLE_SQL = "create table running_statistics (id integer primary key," \
                                      "case_table_name varchar(255),total_case integer,total_time varchar(255)," \
                                      "average_time varchar(255),pass_number integer,fail_number integer," \
                                      "error_number integer,pass_rate real,fail_rate real,error_rate real," \
                                      "create_time TIMESTAMP default (datetime('now', 'localtime'))) "

# 插入接口测试用例数据
INSERT_INTERFACE_DATA_SQL = 'insert into %s (case_id, url, method, request_data, headers, required_struct,' \
                            ' response_result, check_result, response_time, test_error) values (?,?,?,?,?,?,?,?,?,?)'

# 插入用例执行统计结果
INSERT_RUNNING_STATISTICS_DATA_SQL = 'insert into running_statistics (case_table_name, total_case, total_time, ' \
                                     'average_time, pass_number, fail_number, error_number, pass_rate, fail_rate, ' \
                                     'error_rate) values (?,?,?,?,?,?,?,?,?,?)'

# 查询抓包入库的商品ID、bannerID、接口用例数据作为测试用例
SELECT_ITEM_ID_SQL = 'select id, item_id from ItemId'
SELECT_BANNER_ID_SQL = 'select id, banner_id from BannerId'
SELECT_INTERFACE_SQL = 'select * from interface'


def dict_factory(cursor, row):
    """
    以键值对方式返回数据库数据
    :param cursor:
    :param row:
    :return:
    """
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d


class Testr:

    def __init__(self):
        # 初始化数据库
        self.pool = Pool(database="interfaceTest.db")

        # 取出数据库数据
        # self.item_id = self.get_db_data(SELECT_ITEM_ID_SQL)
        # self.banner_id = self.get_db_data(SELECT_BANNER_ID_SQL)
        self.interface = self.get_db_data(SELECT_INTERFACE_SQL)

        # 结果统计
        self.result_pass = 0
        self.result_fail = 0
        self.result_error = 0
        self.total = 0

        # 创建测试用例结果表
        self.table_name = f"test_case_results_{time.strftime('%Y%m%d%H%M', time.localtime(time.time()))}"
        # self.table_name = f"test_case_results_{time.strftime('%Y%m%d%H%M%S', time.localtime(time.time()))}"

    def get_db_data(self, sql):
        """
        获取数据库数据
        :param sql:
        :return:
        """
        conn = self.pool.get()
        result = conn.execute(sql)
        temp = []
        for i in result:
            temp.append(i)
        return temp

    def insert_test_results(self, case_id, url, method, request_data, headers, required_struct,
                            response_result, check_result, response_time, test_error):
        # 插入接口数据
        try:
            conn = self.pool.get()
            conn.execute(INSERT_INTERFACE_DATA_SQL % self.table_name,
                            (case_id, url, method, request_data, headers, required_struct,
                             response_result, check_result, response_time, test_error))
            conn.commit()
            print("\033[1;32m 用例测试结果 数据插入成功 \033[0m")
        except sqlite3.IntegrityError:
            print("\033[1;33m 用例测试结果 数据已存在 \033[0m")

    def request(self, url, params):
        """
        发送请求
        :param url:
        :param params: 请求参数 字典格式
        :return:
        """
        print('\n---------- Start ------------')
        print(f'InterFaceID: {params["id"]}\nURL: {url}\nMethod: {params["method"]}')

        response = ''         # 响应结果
        test_status = 'Fail'  # 测试结果
        test_timer = ''       # 测试时间
        error_reason = ''     # 错误原因

        # 根据请求方式区分
        if params["method"] == "GET":
            try:
                # TODO 增加连接重试次数(一共4次链接)
                sess = requests.Session()
                sess.mount('http://', HTTPAdapter(max_retries=3))
                sess.mount('https://', HTTPAdapter(max_retries=3))
                sess.keep_alive = False  # 关闭多余连接

                resp = requests.get(url=url, params=eval(params['request_data']), headers=eval(params['headers']),
                                    verify=False)

                test_timer = resp.elapsed
                response = resp.json()

                # 是否有响应验证模型
                if params['struct_verify'] is not None:
                    jsonschema.validate(instance=resp.json(), schema=eval(params['struct_verify']))
                    print(f'\033[1;32mTestResults: PASS \033[0m')
                    print(f'\033[1;32m响应时间: {resp.elapsed} \033[0m')
                    self.result_pass += 1
                else:
                    print(f'\033[1;32mTestResults: PASS \033[0m')
                    print(f'\033[1;32m响应时间: {resp.elapsed} \033[0m')
                    self.result_pass += 1

                test_status = 'Pass'

            # 响应模型校验失败
            except jsonschema.exceptions.ValidationError as e:
                print(f'\033[1;31mTestResults: FAIL \033[0m')
                print(f"\033[1;31m验证失败: {e} \033[0m")
                print(f'Response: {resp.json()}')
                test_status = 'Fail'
                error_reason = f'验证失败: {e}'
                self.result_fail += 1

            # 响应不为json格式
            except json.decoder.JSONDecodeError as e:
                print(f'\033[1;31mTestResults: FAIL \033[0m')
                print(f"\033[1;31m接口请求失败: {e} \033[0m")
                response = resp.text
                test_status = 'Error'
                error_reason = f'接口请求失败: {e}\n\n{traceback.format_exc()}'
                self.result_error += 1

            except Exception as e:
                print(f'\033[1;31mTestResults: FAIL \033[0m')
                print(f"\033[1;31m接口请求失败: {e} \033[0m")
                test_status = 'Error'
                error_reason = f'接口请求失败: {e}\n\n{traceback.format_exc()}'
                self.result_error += 1

        elif params["method"] == "POST":
            try:
                # TODO 增加连接重试次数(一共4次链接)
                sess = requests.Session()
                sess.mount('http://', HTTPAdapter(max_retries=3))
                sess.mount('https://', HTTPAdapter(max_retries=3))
                sess.keep_alive = False  # 关闭多余连接

                if params['request_data'] != "":
                    resp = requests.post(url=url, json=eval(params['request_data']), headers=eval(params['headers']),
                                         verify=False)
                else:
                    resp = requests.post(url=url, headers=eval(params['headers']), verify=False)

                test_timer = resp.elapsed
                response = resp.json()

                # 是否有响应验证模型
                if params['struct_verify'] is not None:
                    jsonschema.validate(instance=resp.json(), schema=eval(params['struct_verify']))
                    print(f'\033[1;32mTestResults: PASS \033[0m')
                    print(f'\033[1;32m响应时间: {resp.elapsed} \033[0m')
                    self.result_pass += 1
                else:
                    print(f'\033[1;32mTestResults: PASS \033[0m')
                    print(f'\033[1;32m响应时间: {resp.elapsed} \033[0m')
                    self.result_pass += 1

                test_status = 'Pass'

            # 响应模型校验失败
            except jsonschema.exceptions.ValidationError as e:
                print(f'\033[1;31mTestResults: FAIL \033[0m')
                print(f"\033[1;31m验证失败: {e} \033[0m")
                print(f'Response: {resp.json()}')
                response = resp.json()
                test_status = 'Fail'
                error_reason = f'验证失败: {e}\n\n{traceback.format_exc()}'
                self.result_fail += 1

            # 响应不为json格式
            except json.decoder.JSONDecodeError as e:
                print(f'\033[1;31mTestResults: FAIL \033[0m')
                print(f"\033[1;31m接口请求失败: {e} \033[0m")
                response = resp.text
                test_status = 'Error'
                error_reason = f'接口请求失败: {e}\n\n{traceback.format_exc()}'
                self.result_error += 1

            except Exception as e:
                print(f'\033[1;31mTestResults: FAIL \033[0m')
                print(f"\033[1;31m接口请求失败: {e} \033[0m")
                test_status = 'Error'
                error_reason = f'接口请求失败: {e}'
                self.result_error += 1

        self.insert_test_results(case_id=str(params['id']),
                                 url=url,
                                 method=params['method'],
                                 request_data=params['request_data'],
                                 headers=params['headers'],
                                 required_struct=params['struct_verify'],
                                 response_result=str(response),
                                 check_result=test_status,
                                 response_time=str(test_timer),
                                 test_error=str(error_reason))
        print('----------- End -----------\n')
        self.total += 1

    def main(self):
        try:
            conn = self.pool.get()
            conn.execute(CREATE_RUNNING_STATISTICS_TABLE_SQL)
            print("\033[1;32m 用例运行结果 数据表创建成功 \033[0m")
        except sqlite3.OperationalError as e:
            print(f"\033[1;31m 创建 用例运行结果 表异常 {e} \033[0m")

        try:
            conn = self.pool.get()
            conn.execute(CREATE_INTERFACE_TABLE_SQL % self.table_name)
            print("\033[1;32m 测试用例结果 数据表创建成功 \033[0m")
        except sqlite3.OperationalError as e:
            print(f"\033[1;31m 创建 测试用例结果 表异常 {e} \033[0m")

        interface_list = self.get_db_data(SELECT_INTERFACE_SQL)
        item_id_list = self.get_db_data(SELECT_ITEM_ID_SQL)
        banner_id_list = self.get_db_data(SELECT_BANNER_ID_SQL)

        # random_item_id = random.choice(item_id_list)
        # random_banner_id = random.choice(banner_id_list)
        # print(f"随机 itemId: {random_item_id}\nbannerId: {random_banner_id}")
        start = datetime.datetime.now()
        for i in interface_list:

            # 遍历所有item_id
            if '{item_id}' in i['url']:
                for item_id in item_id_list:
                    url = i['url'].replace('{item_id}', item_id['item_id'])
                    self.request(url=url, params=i)

            # 遍历所有banner_id
            elif '{banner_id}' in i['url']:
                for banner_id in banner_id_list:
                    url = i['url'].replace("{banner_id}", banner_id['banner_id'])
                    self.request(url=url, params=i)

            else:
                url = i['url']
                self.request(url=url, params=i)

        end = datetime.datetime.now()

        total_case = self.total
        total_time = end - start
        average_time = (end - start) / self.total
        pass_number = self.result_pass
        fail_number = self.result_fail
        error_number = self.result_error
        pass_rate = round(self.result_pass / self.total * 100, 2)
        fail_rate = round(self.result_fail / self.total * 100, 2)
        error_rate = round(self.result_error / self.total * 100, 2)

        # 插入接口数据
        conn = self.pool.get()
        try:
            conn.execute(INSERT_RUNNING_STATISTICS_DATA_SQL,
                            (self.table_name, total_case, str(total_time), str(average_time), pass_number,
                             fail_number, error_number, pass_rate, fail_rate, error_rate))
            conn.commit()
            print("\033[1;32m 用例运行结果 数据插入成功 \033[0m")
        except sqlite3.IntegrityError:
            print("\033[1;33m 用例运行结果 数据已存在 \033[0m")

        conn.close()

        print(f'总耗时: {total_time}\t 平均耗时: {average_time}')
        print(f'总用例: {total_case}\t通过: {pass_number}\t失败: {fail_number}\t错误: {error_number}')
        print(f'总用例: {self.total}\t通过率: {pass_rate}%\t失败率: {fail_rate}%\t错误率: {error_rate}%')


if __name__ == '__main__':
    test = Testr()
    test.main()
